/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBlayer;
import Modellayer.*;
import java.sql.*;
import java.util.ArrayList;
/**
 *
 * @author Søren
 */
public class DBSalesOrder implements IFDBSalesOrder{
    private  Connection con;
    /** Creates a new instance of DBSalesOrder */
    public DBSalesOrder() {
      con = DBConnection.getInstance().getDBcon();
    }


  //Implements the methods from the interface
    // get all salesOrder
    public ArrayList<SalesOrder> getAllOrders(boolean retrieveAssociation)
    {
        return miscWhere("", retrieveAssociation);
    }
    //get one salesOrder having the ID
    public SalesOrder findOrder(int salesOrderID, boolean retrieveAssociation)
    {   String wClause = "  salesOrderID = '" + salesOrderID + "'";
        return singleWhere(wClause, retrieveAssociation);
    }

    public SalesOrder findSalesOrder(int salesOrderID)
    {
        SalesOrder proObj = new SalesOrder();
        proObj = singleWhere("salesOrderID = " + salesOrderID, true);
        return proObj;
    }

    public int insertSalesOrder(SalesOrder sale)
    {

       int rc = -1;
	   String query="INSERT INTO persiSalesOrder(customID, salesLineID, dato ,deliveryDate, deliveryStatus)  VALUES('"+
	  		sale.getCustomer().getID()  + "','" +
                        sale.getSalesLine().getSalesLineID() + "','"  +
                        sale.getDato()  + "','"  +
                        sale.getDeliveryDate()  + "','"  +
		    	sale.getDeliveryStatus() + "')";


       System.out.println("insert : " + query);
       try{ // insert new salesOrder
          con.setAutoCommit(false);
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
          con.commit();
          con.setAutoCommit(true);
       }//end try
       catch(SQLException ex){
          System.out.println("SalesOrder ikke oprettet");
          System.out.println("Insert exception in persiSalesOrder db: "+ex);
	  try{
             rc=-1;
             con.rollback();
             con.setAutoCommit(true);
          }
          catch(Exception ex1)
          {
             System.out.println("Error rolling back"+ ex1);
          }
       }
       return(rc);
    }

	public int updateSalesOrder(SalesOrder sale)
	{
		SalesOrder saleObj  = sale;
		int rc=-1;

		String query="UPDATE persiSalesOrder SET "+
		 	  "customID ='"+ saleObj.getCustomer().getID()+"', "+
                          "salesLineID ='"+ saleObj.getSalesLine()+"',"  +
                          "dato ='"+ saleObj.getDato() + "', " +
                          "deliveryStatus ='"+ saleObj.getDeliveryStatus() + "', " +
                          "deliveryDate ='"+ saleObj.getDeliveryDate();

                System.out.println("Update query:" + query);
  		try{ // update salesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in salesorder db: "+ex);
	  	}
		return(rc);
	}

	public void deleteOrder(int ID)
	{
              int rc = -1;

	  	String query="DELETE FROM persiSalesOrder WHERE salesOrderID = '" +
				ID + "'";
                System.out.println(query);
	  	try{ // delete from salesOrder
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in persiSalesOrder db: "+ex);
   	        }
	}

	//private methods
        //michWere is used whenever we want to select more than one salesOrder


	private ArrayList<SalesOrder> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<SalesOrder> list = new ArrayList<SalesOrder>();

	    String query =  buildQuery(wClause);

            try{ // read the salesOrder from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);

		int snr=0;
		while( results.next() ){
	     	 SalesOrder saleObj = new SalesOrder();
		 saleObj = buildSalesOrder(results);
                 list.add(saleObj);
		}//end while
                 stmt.close();
                 if(retrieveAssociation)
                 {   //The customer and salesLineOrder is to be build as well
                     for(SalesOrder saleObj : list){

                        IFDBCustomer dbCustomerObj = new DBCustomer();
                        Customer customerObj = dbCustomerObj.findCustomer(saleObj.getCustomer().getID(), false);
                        System.out.println("Customer is seleceted ");
                        saleObj.setCustomer(customerObj);

//                        IFDBSalesLineItem dbSalesLineItemObj = new DBSalesLineItem();
//                        SalesLineItem SalesLineItemObj = dbSalesLineItemObj.findSalesLine(saleObj., false);
//                        System.out.println("SalesLineItem is seleceted ");
//                        saleObj.addItem(SalesLineItemObj);
                     }
                 }//end if

		}//slut try
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}

	//Singelwhere is used when we only select one SalesOrder
	private SalesOrder singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		SalesOrder saleObj = new SalesOrder();

	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the SalesOrder from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);

	 		if( results.next() )
                        {
                            saleObj = buildSalesOrder(results);
                            //assocaition is to be build
                            stmt.close();
                            System.out.println("salesOrder object 2: "+saleObj+"   "+retrieveAssociation);
                            if(retrieveAssociation)
                            {   //The customer and salesLineOrder is to be build as well
                                System.out.println("look at my horse" + saleObj.getCustomer().getID());
                                IFDBCustomer dbCustomerObj = new DBCustomer();
                               
                                Customer customerObj = dbCustomerObj.findCustomer(saleObj.getCustomer().getID(), false);
                                System.out.println("Customer is seleceted ");
                                saleObj.setCustomer(customerObj);

//                                IFDBSalesLineItem dbSalesLineItemObj = new DBSalesLineItem();
//                                SalesLineItem SalesLineItemObj = dbSalesLineItemObj.findSalesLineItem(saleObj.getItems(), false);
//                                System.out.println("SalesLineItem is seleceted ");
//                                saleObj.addItem(SalesLineItemObj);
                            }
			}
                        else{ //no SalesOrder was found
                            saleObj = null;
                        }
		}//end try
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
                System.out.println("salesOrder object 3: "+saleObj);
		return saleObj;
	}
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT customID, salesLineID, dato, deliveryDate, deliveryStatus  FROM persiSalesOrder";

		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;

		return query;
	}
	//method to build a SalesOrder object
	private SalesOrder buildSalesOrder(ResultSet results)
      {   SalesOrder saleObj = new SalesOrder();
          try{ // the columns from the table perisSalesOrder are used
                saleObj.setCustomer(new Customer(results.getInt("customID")));
                saleObj.setSalesLine(new SalesLineItem());
                saleObj.setDato(results.getString("dato"));
	  	saleObj.setDeliveryDate(results.getString("deliveryDate"));
	  	saleObj.setDeliveryStatus(results.getString("deliveryStatus"));


          }
         catch(Exception e)
         {
             System.out.println("error in building the salesOrder object");
         }
         System.out.println("salesOrder object 1: "+saleObj);
         return saleObj;
      }
}
